<?php
/********************************************************************
 *	Author:
 *		Jestery Liu <jestery@gmail.com>
 *
 *	File:			DBPdoPgsql.class.php
 *	Create Date:	2008-04-18 22:30:01 CST
 *******************************************************************/

require_once(PTE::getCoreLibsPath().'DBException.class.php');

class DBPdoPgsql
{
	protected $host;
	protected $user;
	protected $passwd;
	protected $dbname;
	protected $charset;
	protected $port;
	protected $socket;
	protected $connected = false;
	protected $inTransaction = false;
	protected $affectedRows = 0;
	protected $numQueries = 0;
	protected $sequenceFormat = "%s_%s_seq";
	
	/**
	 * The PDOStatement object
	 *
	 * @var PDOStatement
	 */
	public $stmt;
	
	private $options = null;
	
	/**
	 * The PDO object
	 *
	 * @var PDO
	 */
	private $pdoInstance = null;
	
	const DB_ERROR_NO_SUCH_TABLE = 1146;
	const AUTOQUERY_INSERT = 8;
	const AUTOQUERY_UPDATE = 16;
	
	const FETCH_ASSOC = PDO::FETCH_ASSOC;
	const FETCH_ARRAY = PDO::FETCH_BOTH;
	const FETCH_OBJECT = PDO::FETCH_OBJ;
	const FETCH_NUM = PDO::FETCH_NUM;
	
	protected $fetchMode = self::FETCH_ASSOC;
	
	/**
	 * Constructor. This method not really connect to database. You should use the method 'connect' to connect to the database.
	 *
	 * @param string $host The database server's hostname.
	 * @param string $user
	 * @param string $passwd
	 * @param string $dbname The database name.
	 * @param string $charset
	 * @param string $port
	 * @param string $socket
	 */
	public function __construct($host, $user, $passwd, $dbname, $charset=NULL, $port=NULL, $socket=NULL, $options=null)
	{
		$this->host = $host;
		$this->user = $user;
		$this->passwd = $passwd;
		$this->dbname = $dbname;
		$this->charset = $charset;
		$this->port = $port;
		$this->socket = $socket;
		$this->options = $options;
	}
	
	private function buildDSN() {
		$dsn = array();
		$this->host && $dsn['host'] = "host={$this->host}";
		$this->socket && $dsn['host'] = "socket={$this->socket}";
		$this->dbname && $dsn[] = "dbname={$this->dbname}";
		$this->port && $dsn[] = "port={$this->port}";
		$this->user && $dsn[] = "user={$this->user}";
		$this->passwd && $dsn[] = "password={$this->passwd}";
		return 'pgsql:' . implode(' ', $dsn);
	}

	/**
	 * Connects to database.
	 * If the connection has already established, return true directly, else, connect.
	 *
	 * @return true|false
	 */
	public function connect()
	{
		if ($this->connected)
		{
			return true;
		}
		else
		{
			try {
				$this->pdoInstance = is_array($this->options) ? new PDO($this->buildDSN(), $this->user, $this->passwd, $this->options) : new PDO($this->buildDSN());
			} catch (PDOException $e) {
				throw new DBException('Error connecting to database: '.$e->getMessage(), DBException::ERROR_CONNECT);
				return false;
			}
			$this->pdoInstance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
			$this->charset && $this->setCharset();
			$this->connected = true;
			return true;
		}
	}

	/**
	 * Sets the default character set to be used when sending data from and to the database server.
	 *
	 * @param string $charset
	 * @return true|false
	 */
	public function setCharset($charset = null)
	{
		if (is_null($charset))
			$charset = $this->charset;
		$this->connect();
		$bool = @$this->pdoInstance->exec("SET NAMES $charset");
		if ($bool===false)
		{
			throw new DBException('Error setting client encoding: '.$this->getErrorMessage(), DBException::ERROR_SETTING_CHARSET);
			return false;
		}
		else
		{
			return true;
		}
	}

	/**
	 * Closes a previously opened database connection
	 *
	 * @return true
	 */
	public function close()
	{
		if ($this->connected)
		{
			$this->stmt = null;
			$this->pdoInstance = null;
			$this->connected = false;
		}
		return true;
	}

	/**
	 * Starts a transaction.
	 *
	 * @return true|false;
	 */
	public function beginTransaction()
	{
		$this->connect();
		$bool = $this->pdoInstance->beginTransaction();
		$error = $this->getErrorMessage();
		if ($error)
		{
			throw new DBException('Error starting transaction: '.$error, DBException::ERROR_TRANSACTION);
		}
		return $this->inTransaction;
	}

	/**
	 * Commits the current transaction
	 *
	 * @return true|false;
	 */
	public function commit()
	{
		$bool = true;
		if ($this->inTransaction)
		{
			$bool = $this->pdoInstance->commit();
		}
		$error = $this->getErrorMessage();
		if ($error)
		{
			throw new DBException('Error commiting transaction: '.$error, DBException::ERROR_TRANSACTION);
		}
		return $bool;
	}

	/**
	 * Rolls back current transaction.
	 *
	 * @return true|false;
	 */
	public function rollBack()
	{
		$bool = $this->pdoInstance->rollBack();
		$error = $this->getErrorMessage();
		if ($error)
		{
			throw new DBException('Error rolling back transaction: '.$error, DBException::ERROR_TRANSACTION);
		}
		return $bool;
	}

	/**
	 * Escapes special characters in a string for use in a SQL statement.
	 *
	 * @param string $string
	 * @return string
	 */
	public function escape($string)
	{
		if (function_exists('pg_escape_string')) {
            return pg_escape_string($string);
        } else {
            return str_replace("'", "''", str_replace('\\', '\\\\', $string));
        }
	}

	/**
	 * Formats input so it can be safely used in a query.
	 *
	 * @param mixed $in
	 * @return mixed The formatted data.
	 */
	public function quote($in)
	{
		if (is_int($in))
		{
			return $this->pdoInstance->quote($in, PDO::PARAM_INT);
		}
		elseif (is_float($in))
		{
			return $this->quoteFloat($in);
		}
		elseif (is_bool($in))
		{
			return $this->pdoInstance->quote($in, PDO::PARAM_BOOL);
		}
		elseif (is_null($in))
		{
			return $this->pdoInstance->quote($in, PDO::PARAM_NULL);
		}
		else
		{
			return $this->pdoInstance->quote($in, PDO::PARAM_STR);
		}
	}

	/**
	 * Prepare a SQL statement for execution.
	 *
	 * @param string $query This parameter can include one or more parameter markers
	 *  in the SQL statement by embedding question mark (?) characters at the appropriate positions.
	 * @return true|false;
	 */
	public function prepare($query, $options = null)
	{
		$this->connect();
		$this->stmt = @$this->pdoInstance->prepare($query, $options);
		$error = $this->getErrorMessage();
		if ($error || $this->stmt===false)
		{
			throw new DBException('Error preparing statement: '.$error, DBException::ERROR_PREPARING);
			return false;
		}
		else
		{
			return true;
		}
	}

	/**
	 * Execute a query to the database.
	 *
	 * @param string $query
	 * @return affectedRows|false;
	 */
	public function exec($query)
	{
		$this->connect();
		$result = $this->pdoInstance->exec($query);
		if ($result===false || '' != $this->getErrorMessage())
		{
			$error = $this->getErrorMessage();
			throw new DBException('Error querying database: '.$error."\nSQL:\n$query", DBException::ERROR_QUERYING);
			$this->affectedRows = 0;
		} else {
			$this->affectedRows = $result;
			$this->numQueries++;
		}
		return $result;
	}

	/**
	 * Runs a query.
	 * @param string $query SQL query.
	 * @return PDOStatement;
	 */
	public function query($query)
	{
		$this->connect();
		$this->stmt = $this->pdoInstance->query($query);
		if ($this->stmt===false) {
			throw new DBException('Error quering database: ' . $this->getErrorMessage());
		}
		$this->numQueries++;
		return $this->stmt;
	}
	
	public function queryColumn($query, $colno) {
		$this->connect();
		$this->stmt = $this->pdoInstance->query($query, PDO::FETCH_COLUMN, $colno);
		$this->numQueries++;
		return $this->stmt;
	}
	
	public function queryClass($query, $className, array $args) {
		$this->connect();
		$this->stmt = $this->pdoInstance->query($query, PDO::FETCH_CLASS, $className, $args);
		$this->numQueries++;
		return $this->stmt;
	}
	
	public function queryObject($query, object &$object) {
		$this->connect();
		$this->stmt = $this->pdoInstance->query($query, PDO::FETCH_INTO, $object);
		$this->numQueries++;
		return $this->stmt;
	}
	
	public function getAttribute($attr) {
		$this->connect();
		return $this->pdoInstance->getAttribute($attr);
	}
	
	public function getPdoInstance() {
		$this->connect();
		return $this->pdoInstance;
	}
	
	public function getStatementInstance() {
		return $this->stmt;
	}

	/**
	 * Automatically generates an insert or update query.
	 *
	 * @param string $tableName		The table name.
	 * @param array $fieldsValues	The associative array where key is a field name and value its value.
	 * @param integer $mode			A type of query to make: DB::AUTOQUERY_INSERT or DB::AUTOQUERY_UPDATE
	 * @param string $where			For update queries: the WHERE clause to append to the SQL statement.
	 * 								Don't include the "WHERE" keyword.
	 * 								NOTE: The value in 'WHERE' statement MUST be quouted.
	 * @return true|false
	 * 
	 * @see DB::buildSql()
	 */
	public function autoExecute($tableName, array $fieldsValues, $mode=self::AUTOQUERY_INSERT, $where=false)
	{
		if (count($fieldsValues)<1)
		{
			throw new DBException('Need more data to execute.', DBException::ERROR_EXECUTE);
		}
		$fields = array();
		$values = array();
		foreach ($fieldsValues as $field=>$value)
		{
			array_push($fields, $field);
			array_push($values, $value);
		}
		$sql = $this->buildSql($tableName, $fields, $mode, $where);
		$this->connect();
		$stmt = $this->pdoInstance->prepare($sql);
		$bool = $stmt->execute($values);
		unset($sql, $stmt, $fields, $values);
		return $bool;
	}

	/**
	 * Performs several PDOStatement::execute() calls on the prepared statement handle.
	 * 
	 * $allData must be an array indexed numerically from 0,
	 * one execute call is done for every "row" in the array.
	 * 
	 * If an error occurs during execute(), executeMultiple() does not execute the unfinished rows.
	 * 
	 * example:
	 * <code>
	 * $allData = array(array('v1-1', 'v1-2'), array('v2-1','v2-4');
	 * $db->prepare("INSERT INTO some_table('field1', 'field2') values(?,?)");
	 * $db->executeMultiple($allData);
	 * </code>
	 *
	 * @param array $allData Numeric array containing the data to insert into the query.
	 * @return true
	 * 
	 * @see DBPdoPgsql::prepare()
	 */
	public function executeMultiple(array $allData)
	{
		if (!$this->stmt instanceof PDOStatement)
		{
			throw new DBException('You must call DBPdoPgsql::prepare() first.', DBException::ERROR_EXECUTE);
		}
		if (count($allData)<1 || count($allData[0])<1)
		{
			throw new DBException('Need more data to execute.', DBException::ERROR_EXECUTE);
		}
		$this->beginTransaction();
		@$this->stmt->closeCursor();
		foreach ($allData as $data)
		{
			$res = $this->stmt->execute($data);
			if (!$res)
			{
				$this->rollBack();
				throw new DBException("Can't execute statement: ".$this->getErrorMessage(), DBException::ERROR_EXECUTE);
			}
			$this->numQueries++;
		}
		$this->commit();
		return true;
	}

	/**
	 * Produces an SQL query string.
	 * Example:
	 * <code>
	 * $this->buildSql('some_table', array('f1','f2'));
	 * </code>
	 * That returns:
	 * INSERT INTO some_table (f1, f2) VALUES (?,?)
	 *
	 * @param string $table			The table name.
	 * @param array $tableFields	The array of field names.
	 * @param integer $mode			A type of query to make: self::AUTOQUERY_INSERT or self::AUTOQUERY_UPDATE
	 * @param string $where			For update queries: the WHERE clause to append to the SQL statement.
	 * 								Don't include the "WHERE" keyword.
	 * @return string				The sql query for DB::query or mysqli_stmt::prepare.
	 */
	private function buildSql($table, array $tableFields, $mode, $where = false)
	{
		if (count($tableFields) == 0)
		{
			throw new DBException("Need more data.", DBException::ERROR_QUERYING);
		}
		$first = true;
		switch ($mode) {
			case self::AUTOQUERY_INSERT:
				$values = '';
				$names = '';
				foreach ($tableFields as $value) {
					if ($first)
					{
						$first = false;
					}
					else
					{
						$names .= ',';
						$values .= ',';
					}
					$names .= '"'.$value.'"';
					$values .= '?';
				}
				return "INSERT INTO \"$table\" ($names) VALUES ($values)";
			case self::AUTOQUERY_UPDATE:
				$set = '';
				foreach ($tableFields as $value) {
					if ($first)
					{
						$first = false;
					}
					else
					{
						$set .= ',';
					}
					$set .= "\"{$value}\"=?";
				}
				$sql = "UPDATE \"{$table}\" SET {$set}";
				if ($where)
				{
					$sql .= " WHERE {$where}";
				}
				return $sql;
			default:
				throw new DBException('No such mode.', DBException::ERROR_NO_SUCH_MODE);
		}
	}

	/**
	 * Performs a query and get the specified column position's value of first row.
	 *
	 * @param string $query
	 * @param array $data
	 * @return The value that column contians.
	 */
	public function getCol($query, $col=0, array $data=array())
	{
		$this->connect();
		$stmt = $this->pdoInstance->prepare($query);
		$bool = $stmt->execute($data);
		if (!$bool) {
			throw new DBException('Error quering database: ' . $this->getErrorMessage());
		}
		$rtn = $stmt->fetchColumn($col);
		unset($stmt);
		return $rtn;
	}

	/**
	 * Performs a query and get the value of first row's first column.
	 *
	 * @param string $query
	 * @param array $data
	 * @return The value that column contians.
	 * 
	 * @see DB::getCol()
	 */
	public function getOne($query, array $data=array())
	{
		$this->connect();
		return $this->getCol($query, 0, $data);
	}

	/**
	 * Performs a query and return all result as an array.
	 *
	 * @param string $query
	 * @param array $data
	 * @return array|false
	 * 
	 * @see DB::fetch(), $DB::query()
	 */
	public function getAll($query, array $data=array())
	{
		$this->connect();
		$stmt = $this->pdoInstance->prepare($query);
		$bool = $stmt->execute($data);
		if (!$bool) {
			throw new DBException('Error quering database: ' . $this->getErrorMessage());
		}
		$rtn = $stmt->fetchAll($this->fetchMode);
		unset($stmt);
		return $rtn;
	}
	
	/**
	 * Performs a query and return the first row in result.
	 *
	 * @param string $query
	 * @param array $data
	 * @return array|false
	 */
	public function getRow($query, array $data=array())
	{
		$this->connect();
		$stmt = $this->pdoInstance->prepare($query);
		$bool = $stmt->execute($data);
		if (!$bool) {
			throw new DBException('Error quering database: ' . $this->getErrorMessage());
		}
		$rtn = $stmt->fetch($this->fetchMode);
		unset($stmt);
		return $rtn;
	}
	
	public function setFetchMode($mode) {
		$this->fetchMode = $mode;
	}

	/**
	 * Fetch a result row using a fetchmode.
	 *
	 * @param object $result The query result.
	 * @param integer $mode One of DB::FETCH_ASSOC, DB::FETCH_ARRAY, DB::FETCH_OBJECT, DB::FETCH_NUM. Default is FETCH_ASSOC.
	 * @return array|object
	 * 
	 * @see DB::setFetchMode()
	 */
	public function fetch($mode=null, $stmt=null)
	{
		if (!$stmt instanceof PDOStatement && !$this->stmt instanceof PDOStatement) {
			throw new DBException('There is no PDOStatement yet.', DBException::ERROR_NO_RESULTSET);
		}
		if (is_null($mode)) {
			$mode = $this->fetchMode;
		}
		return $stmt == null ? $this->stmt->fetch($mode) : $stmt->fetch($mode);
	}

	/**
	 * Returns the auto generated id used in the last query.
	 *
	 * @return integer
	 */
	public function getLastInsertId($seqName)
	{
		return $this->lastInsertId($seqName);
	}
	
	public function lastInsertId($seqName) {
		return $this->pdoInstance->lastInsertId($seqName);
	}

	/**
	 * Gets the number of affected rows in the last query.
	 *
	 * @return integer
	 */
	public function getAffectedRows()
	{
		return $this->affectedRows;
	}

	/**
	 * Gets the total queries.
	 *
	 * @return integer
	 */
	public function getNumQueries()
	{
		return $this->numQueries;
	}
	/**
	 * Gets the string that describes the last error message of pdo or pdostatement object.
	 *
	 * @return string If no error occurred, returns an empty string.
	 */
	public function getErrorMessage()
	{
		$msg = '';
		if (is_object($this->pdoInstance))
		{
			$msg = $this->pdoInstance->errorInfo();
			is_array($msg) && $msg = $msg[2];
		}
		if (empty($msg) && is_object($this->stmt))
		{
			$msg = $this->stmt->errorInfo();
			is_array($msg) && $msg = $msg[2];
		}
		return $msg;
	}

	/**
	 * Returns the next free id in a sequence.
	 *
	 * @param string $table Name of the table.
	 * @param string $fieldName
	 * @return integer The next id number in the sequence.
	 */
	public function nextId($table, $fieldName)
	{
		$this->connect();
		$seqName = $this->getSeqName($table, $fieldName);
		$stmt = $this->pdoInstance->query("SELECT nextval('{$seqName}')");
		$nextId = $stmt->fetchColumn(0);
		$error = $this->getErrorMessage();
		if ($error) {
			throw new DBException("Can't get the nextId: ".$error, DBException::ERROR_NEXT_ID);
		}
		unset($stmt);
		return $nextId;
	}

	/**
	 * Generates the name used inside the database for a sequence.
	 *
	 * @param string $table table name.
	 * @param string $field The name of a column which type is 'serial'
	 * @return string The sequence name.
	 */
	protected function getSeqName($table, $field)
	{
		return sprintf($this->sequenceFormat, $table, $field);
	}

	/**
	 * frees result memory
	 *
	 */
	public function freeResult()
	{
		@$this->stmt->closeCursor();
		unset($this->stmt);
	}

	public function __destruct()
	{
		unset($this->stmt);
	}
}